package zhuzx.solution.ir;

import java.util.List;
import java.util.Map;

import javax.swing.JOptionPane;

import org.zhuzx.util.GeneralUtil;
import org.zhuzx.util.JdbcUtil;

import zhuzx.generator.GuiUtil;

public class IrDataHelper {
	
	static class IrUser {
		/**
		 * user_id，即unno。
		 */
		String id;
		String name;
		/**
		 * 内部组织名
		 */
		String orgName;
		/**
		 * 以斜杠分隔的组织全称
		 */
		String fullOrgName;
		
		public IrUser(String id, String name, String orgName, String fullOrgName) {
			this.id = id;
			this.name = name;
			this.orgName = orgName;
			this.fullOrgName = fullOrgName;
		}
	}

	/**
	 * 本方法的主要用途在于找到用户所在的公司（也就是最小权限单位），传入的参数可以是更上级的部门以辅助定位。<br>
	 * 限制：不适用于新员工、新建部门的员工，必须在ir_qywx_org表中能找到完整组织信息才有结果返回。
	 * 根据address表中的qywx_org_name跟org表做内连接。
	 */
	static IrUser findUser(String name, String org) {
		if (GeneralUtil.isBlank(name)) {
			throw new IllegalArgumentException("name不能为空");
		}
		//反向放置
		String[] orgFields = {"org_level_four_name", "org_level_there_name", "org_level_two_name"};
		String sql = "select t1.user_id, t1.name, t1.phone_number, t1.qywx_org_name,\r\n" + 
				"t2.%s, t2.%s, t2.%s\r\n" + 
				"from oa.ir_address_book t1 left join oa.ir_qywx_org t2\r\n" + 
				"on t1.qywx_org_name = t2.org_name\r\n" + 
				"where t1.name = '" + name + "'";
		sql = String.format(sql, (Object[]) orgFields);
		if (GeneralUtil.isNotBlank(org)) {
			sql += createOrgExp("t1.qywx_org_name", org);
		}
		List<Map<String,String>> list = query("findUser", sql, name);
		if (list.size() != 1) {
			return null;
		}
		Map<String,String> info = list.get(0);
		String exactOrg = null;
		for (String field : orgFields) {
			exactOrg = info.get(field.toUpperCase());
			if (exactOrg != null && !exactOrg.equals("99999")) {
				break;
			} else {
				exactOrg = null;
			}
		}
		return new IrUser(info.get("USER_ID"), info.get("NAME"),
				exactOrg, info.get("QYWX_ORG_NAME"));
	}
	
	static String createNamePhoneExp(String name, String phone) {
		String exp = "";
		if (GeneralUtil.isNotBlank(name)) {
			exp += "\nand name = '" + name + "'";
		}
		if (GeneralUtil.isNotBlank(phone)) {
			exp += "\nand phone_number = '" + phone + "'";
		}
		return exp;
	}
	
	static String createOrgExp(String field, String org) {
		if (GeneralUtil.isBlank(org)) {
			return "";
		}
		String convertedOrg = convertBrackets(org);
		String exp = "\nand (" + field + " like '%" + org + "%'" +
				" or " + field + " like '%" + convertedOrg + "%')";
		return exp;
	}
	
	static String createTimeExp(String field, int daysOffset) {
		String timeClause;
		if (daysOffset != 0) {
			String[] times = GeneralUtil.getTimestamps(daysOffset);
			timeClause = String.format("\nand " + field + " >= '%s' and " + field + " <= '%s'",
					times[0], times[1]);
		} else {//更易读
			timeClause = "\nand " + field + " > '" + GeneralUtil.getDate() + "'";
		}
		return timeClause;
	}
	
	/**
	 * 中英文括号互转
	 */
	public static String convertBrackets(String str) {
		String[][] targets = {
				{"（", "("},
				{"）", ")"}
		};
		for (String[] pair : targets) {
			if (str.contains(pair[0])) {
				str = str.replace(pair[0], pair[1]);
			} else if (str.contains(pair[1])) {
				str = str.replace(pair[1], pair[0]);
			}
		}
		return str;
	}
	
	public static List<Map<String,String>> query(String desc, String sql, String key) {
		System.out.println(desc + " sql :\n" + sql);
		List<Map<String,String>> list = JdbcUtil.executeQuery(sql);
		System.out.println("结果：\n" + (list.size()==0 ? key+"：查询无数据。" : "") +
				GeneralUtil.getCombinedString(list, "\n"));
		return list;
	}
	
	/**
	 * 无记录或者查询到多条，都返回null。
	 */
	public static Map<String,String> queryUnique(String desc, String sql, String key) {
		List<Map<String,String>> list = query(desc, sql, key);
		return (list.size() == 1) ? list.get(0) : null;
	}
	
	public static int update(String desc, String sql, String key) {
		System.out.println(desc + " sql :\n" + sql);
		int rows = JdbcUtil.executeUpdate(sql);
		System.out.println(key + "：" + rows + "行受到影响");
		return rows;
	}
	
	public static void showResult(String msg) {
		if (msg != null) {
			JOptionPane.showMessageDialog(null,
					msg, "成功", JOptionPane.PLAIN_MESSAGE);
			System.out.println("\n成功，文本已复制到剪贴板，全文如下：\n" + msg);
			GuiUtil.sendToClipboard(msg);
		} else {
			msg = "无结果，需要人工介入！";
			JOptionPane.showMessageDialog(null,
					msg, "失败", JOptionPane.ERROR_MESSAGE);
			System.out.println("\n失败：\n" + msg);
		}
		System.exit(0);
	}
	
	/**
	 * 适用于字符形式的自增数字主键，
	 * 注意：address表不能用这个，因为已经有记录将user_id作为id插进去了，根据这个自增，会有很大的视觉误导。
	 */
	static String createId(String tableName) {
		String maxId = JdbcUtil.executeQuery("select max(to_number(id)) as MAXID from "
				+ tableName).get(0).get("MAXID");
		return String.valueOf(Integer.parseInt(maxId) + 1);
	}
	
	static Map<String,String> getAddressBookInfo(String name, String phone) {
		String sql = "select * from OA.IR_ADDRESS_BOOK where 1=1";
		sql += createNamePhoneExp(name, phone);
		return queryUnique("getAddressBookInfo", sql, 
				GeneralUtil.concatStringExcludeBlank("", name, phone));
	}

}